﻿using System;
using System.Collections.Generic;
using System.Text;
using KPIS.DBM;
using System.Configuration;
using KPIS.GERP.GIMS.MODEL;
using System.Data;

namespace KPIS.GERP.GIMS.DAL
{
    public class D_IcmInventoryStockTakingItem
    {
        DBManager dbManager = new DBManager(DataProvider.MySQL, ConfigurationSettings.AppSettings["ConnectionString"].ToString());
        ConvertNullable cn = new ConvertNullable();

        public IList<M_IcmInventoryStockTakingItem> GetListFillStockTakingCode(string icm_stock_taking_code)
        {
            string strSQL;
            DataRowCollection drc = null;
            IList<M_IcmInventoryStockTakingItem> infoList = new List<M_IcmInventoryStockTakingItem>();
            try
            {
                dbManager.Open();
                strSQL = " SELECT icm_stock_taking_item_seq,icm_stock_taking_code,stock_taking.icm_res_item_seq,quanlity_amount_stock "
                        +" ,quantity_amount_real,remark,icm_resource.icm_res_item_code,icm_resource.icm_res_item_name "
                        +" ,concat(COALESCE(icm_resource.icm_res_item_code,''),'',COALESCE(icm_resource.icm_res_item_name)) as resource_name "
                        +" FROM icm_inventory_stock_taking_item stock_taking "
                        +" LEFT JOIN icm_mas_resource_item icm_resource ON stock_taking.icm_res_item_seq = icm_resource.icm_res_item_seq "
                        + " WHERE stock_taking.record_status <>'D' and icm_stock_taking_code LIKE '%' '" + icm_stock_taking_code + "'  '%' ";

                drc = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0].Rows;
                if (drc.Count > 0)
                {
                    for (int i = 0; i < drc.Count; i++)
                    {
                        M_IcmInventoryStockTakingItem info = new M_IcmInventoryStockTakingItem();
                        info.icm_stock_taking_item_seq = Convert.ToInt32(drc[i]["icm_stock_taking_item_seq"].ToString());
                        info.icm_stock_taking_code = drc[i]["icm_stock_taking_code"].ToString();
                        info.icm_res_item_seq = cn.ConvertNullInt(drc[i]["icm_res_item_seq"].ToString());
                        info.resource_name = drc[i]["resource_name"].ToString();
                        info.quanlity_amount_stock = cn.ConvertNullInt(drc[i]["quanlity_amount_stock"]);
                        info.quantity_amount_real = cn.ConvertNullInt(drc[i]["quantity_amount_real"].ToString());
                        info.remark = drc[i]["remark"].ToString();
                        info.resource_name = drc[i]["resource_name"].ToString();
                        infoList.Add(info);
                    }
                }
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }

            return infoList;
        }

        public M_IcmInventoryStockTakingItem GetListFillBySeq(string icm_stock_taking_item_seq)
        {
            string strSQL;
            DataRowCollection drc = null;
            M_IcmInventoryStockTakingItem info = new M_IcmInventoryStockTakingItem();
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(1);
                dbManager.AddParameters(0, "@icm_stock_taking_item_seq", icm_stock_taking_item_seq);

                strSQL = " SELECT icm_stock_taking_item_seq,icm_stock_taking_code,stock_taking.icm_res_item_seq,quanlity_amount_stock "
                        + " ,quantity_amount_real,remark,icm_resource.icm_res_item_code,icm_resource.icm_res_item_name "
                        + " ,concat(COALESCE(icm_resource.icm_res_item_code,''),'',COALESCE(icm_resource.icm_res_item_name)) as resource_name "
                        + " FROM icm_inventory_stock_taking_item stock_taking "
                        + " LEFT JOIN icm_mas_resource_item icm_resource ON stock_taking.icm_res_item_seq = icm_resource.icm_res_item_seq "
                        + " WHERE stock_taking.record_status <>'D' and  icm_stock_taking_item_seq = @icm_stock_taking_item_seq ";

                drc = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0].Rows;
                if (drc.Count > 0)
                {
                    info.icm_stock_taking_item_seq = Convert.ToInt32(drc[0]["icm_stock_taking_item_seq"].ToString());
                    info.icm_stock_taking_code = drc[0]["icm_stock_taking_code"].ToString();
                    info.icm_res_item_seq = cn.ConvertNullInt(drc[0]["icm_res_item_seq"]);
                    info.quanlity_amount_stock = cn.ConvertNullInt(drc[0]["quanlity_amount_stock"]);
                    info.quantity_amount_real = cn.ConvertNullInt(drc[0]["quantity_amount_real"]);
                    info.remark = drc[0]["remark"].ToString();
                    info.resource_name = drc[0]["resource_name"].ToString();
                }
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }

            return info;
        }
        public void Insert_StockTaking_Item(M_IcmInventoryStockTakingItem info)
        {
            string strSQL;
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(8);
                dbManager.AddParameters(0, "@icm_stock_taking_code", info.icm_stock_taking_code);
                dbManager.AddParameters(1, "@icm_res_item_seq", info.icm_res_item_seq);
                dbManager.AddParameters(2, "@quanlity_amount_stock", info.quanlity_amount_stock);
                dbManager.AddParameters(3, "@quantity_amount_real", info.quantity_amount_real);
                dbManager.AddParameters(4, "@remark", info.remark);
                dbManager.AddParameters(5, "@record_status", info.record_status);
                dbManager.AddParameters(6, "@created_when", info.created_when);
                dbManager.AddParameters(7, "@created_by", info.created_by);

                strSQL = " INSERT INTO icm_inventory_stock_taking_item "
                        + " (icm_stock_taking_code,icm_res_item_seq,quanlity_amount_stock,quantity_amount_real "
                        + " ,remark,record_status ,created_when,created_by )"
                        + "  VALUES "
                        + "(@icm_stock_taking_code,@icm_res_item_seq,@quanlity_amount_stock,@quantity_amount_real "
                        + " ,@remark,@record_status ,@created_when,@created_by )";
                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public void Update_StockTaking_Item(M_IcmInventoryStockTakingItem info)
        {
            string strSQL;
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(8);
                dbManager.AddParameters(0, "@icm_stock_taking_code", info.icm_stock_taking_code);
                dbManager.AddParameters(1, "@icm_res_item_seq", info.icm_res_item_seq);
                dbManager.AddParameters(2, "@quanlity_amount_stock", info.quanlity_amount_stock);
                dbManager.AddParameters(3, "@quantity_amount_real", info.quantity_amount_real);
                dbManager.AddParameters(4, "@remark", info.remark);
                dbManager.AddParameters(5, "@record_status", info.record_status);
                dbManager.AddParameters(6, "@updated_by", info.updated_by);
                dbManager.AddParameters(7, "@updated_when", info.updated_when);

                strSQL = " UPDATE  icm_inventory_stock_taking_item "
                         + "  SET icm_res_item_seq = @icm_res_item_seq "
                         + " ,quanlity_amount_stock = @quanlity_amount_stock "
                         + " ,quantity_amount_real = @quantity_amount_real "
                         + " ,remark = @remark "
                         + " ,record_status = @record_status "
                         + " ,updated_by = @updated_by "
                         + " ,updated_when = @updated_when "
                         + " WHERE icm_stock_taking_code = @icm_stock_taking_code ";

                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }
        }
        public void Delete_StockTaking_Item(M_IcmInventoryStockTakingItem info)
        {
            string strSQL;
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(4);
                dbManager.AddParameters(0, "@icm_stock_taking_item_seq", info.icm_stock_taking_item_seq);
                dbManager.AddParameters(1, "@record_status", info.record_status);
                dbManager.AddParameters(2, "@updated_by", info.updated_by);
                dbManager.AddParameters(3, "@updated_when", info.updated_when);

                strSQL = " UPDATE  icm_inventory_stock_taking_item "
                         + "  SET record_status = @record_status "
                         + " ,updated_by = @updated_by "
                         + " ,updated_when = @updated_when "
                         + " WHERE icm_stock_taking_item_seq = @icm_stock_taking_item_seq ";

                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }
        }

    }
}
